const db = require('../config/mysql');
const dayjs = require('dayjs');
const {or} = require("sequelize");

class ProcurementOrderModel {
    constructor() {
        this.db = db;
        // 定义核心表名（与数据库完全一致）
        this.orderTable = 'goods_order';          // 采购订单主表
        this.orderDetailTable = 'goods_order_item';// 采购订单明细表
        this.goodsTable = 'goods';               // 食材表（关联商品信息）
        this.supplierTable = 'supplier';         // 供应商表（关联供应商信息）
        this.userTable = 'user';                 // 用户表（关联创建人/操作人）
        this.categoryTable = 'category';         // 食材分类表（关联分类信息）
    }

    /**
     * 获取所有采购订单（关联供应商、创建人、商品信息，支持筛选和分页）
     * @param {Object} query - 筛选条件（状态、日期、供应商、商品等）
     * @returns {Object} 订单列表 + 分页信息
     */
    async getAllOrders(query = {}) {
        let {
            status,          // 订单状态
            startDate,       // 订单创建开始日期
            endDate,         // 订单创建结束日期
            supplierId,      // 供应商ID（从明细表筛选）
            goodsName,       // 商品名称
            page = 1,        // 页码
            pageSize = 10,    // 每页条数,
            //新增
            created_uid,  // 创建人ID
            code,         // 订单编号
            order_date,   // 归属日期
            urgency_level // 紧急程度：1-普通, 2-紧急, 3-非常紧急-add
        } = query;

        const offset = (page - 1) * pageSize;
        const conditions = [];
        const params = [];

        // 1. 构建筛选条件（保持不变）
        if (status !== undefined && status !== null && status !== '') {
            conditions.push('go.status = ?');
            params.push(Number(status));
        }
        // 创建订单时间
        if (startDate) {
            conditions.push('go.order_date >= ?');
            params.push(startDate);
        }
        if (endDate) {
            conditions.push('go.order_date <= ?');
            params.push(endDate);
        }
        // 供应商ID仍从明细表筛选
        if (supplierId !== undefined && supplierId !== null && supplierId !== '') {
            conditions.push('goi.supplier_id = ?');
            params.push(Number(supplierId));
        }

        if (goodsName) {
            conditions.push('g.name LIKE ?');
            params.push(`%${goodsName}%`);
        }

        // 新增条件
        if (created_uid) {
            conditions.push('go.created_uid = ?');
            params.push(Number(created_uid));
        }

        if (code) {
            conditions.push('go.code LIKE ?');
            params.push(`%${code}%`);
        }

        if (order_date) {
            conditions.push('go.order_date = ?');
            params.push(order_date);
        }

        if (urgency_level) {
            conditions.push('go.urgency_level = ?');
            params.push(Number(urgency_level));
        }

        // 2. 查询订单ID（保持不变）
        let orderIdsSql = `
            SELECT DISTINCT go.order_id, go.order_date 
            FROM ${this.orderTable} go
            LEFT JOIN ${this.orderDetailTable} goi ON go.order_id = goi.order_id
            LEFT JOIN ${this.goodsTable} g ON goi.goods_id = g.goods_id
            WHERE 1=1
            ${conditions.length > 0 ? 'AND ' + conditions.join(' AND ') : ''}
            ORDER BY go.order_date DESC  
            LIMIT ?, ?
        `;
        const orderIdsParams = [...params, offset, Number(pageSize)];
        const [orderIdsResult] = await this.db.query(orderIdsSql, orderIdsParams);
        const orderIds = orderIdsResult.map(item => item.order_id);

        // 3. 查总数（保持不变）
        const countSql = `
            SELECT COUNT(DISTINCT go.order_id) AS total
            FROM ${this.orderTable} go
            LEFT JOIN ${this.orderDetailTable} goi ON go.order_id = goi.order_id
            LEFT JOIN ${this.goodsTable} g ON goi.goods_id = g.goods_id
            WHERE 1=1
            ${conditions.length > 0 ? 'AND ' + conditions.join(' AND ') : ''}
        `;
        const [countResult] = await this.db.query(countSql, params);
        const total = Number(countResult[0].total || 0);

        if (orderIds.length === 0) {
            return {
                list: [],
                pagination: { total, page: Number(page), pageSize: Number(pageSize), totalPages: Math.ceil(total / pageSize) }
            };
        }

        // 4. 批量查询订单主表信息（移除供应商相关字段，避免GROUP BY冲突）
        const mainOrdersSql = `
            SELECT 
                go.order_id,
                go.code AS order_code,
                go.title,                      -- 订单标题
                go.order_date,                 -- 订单创建时间
                go.total_amount,               -- 订单总金额
                go.status,                     -- 订单状态
                go.created_uid,                -- 创建人ID
                u.user_name AS creator_name,   -- 创建人姓名
                go.notes AS order_notes,       -- 订单备注
                go.urgency_level,              -- 紧急程度
                go.order_date,                 -- 归属日期
                go.notes AS order_notes,       -- 订单备注
                go.created_at,
                go.updated_at,
                (select sum(ordered_count) from goods_order_item as goi where goi.order_id = go.order_id) as goods_count
            FROM ${this.orderTable} go
            LEFT JOIN ${this.userTable} u ON go.created_uid = u.user_id
            WHERE go.order_id IN (${orderIds.map(() => '?').join(',')})
            ORDER BY go.order_id DESC
        `;
        // 主表查询不再关联明细表和供应商表，无需GROUP BY
        const [mainOrders] = await this.db.query(mainOrdersSql, orderIds);

        // 5. 批量查询订单详情（保留并完善供应商信息）
        const orderDetailsSql = `
            SELECT 
                goi.item_id,
                goi.order_id,
                goi.goods_id,                 -- 商品ID
                goi.supplier_id,              -- 明细表中的供应商ID
--                 g.name AS goods_name,         -- 商品名称
                goi.spec,                     -- 规格
                goi.unit,                     -- 商品单位
                s.supplier_name,              -- 供应商名称
                s.contact_person,             -- 供应商联系人
                s.phone AS supplier_phone,     -- 供应商电话
                goi.ordered_count,            -- 订购数量
                goi.unit_price,               -- 单价
                goi.amount,                   -- 单个商口汇总金额
                goi.notes AS item_notes,     -- 备注
                ctg.name as cate_name,         -- 食材分类表
                ctg.cate_id,                     -- 分类id -- 11.27 新加，把goods表数据同步一分到订单明细表
--                 g.out_price,                   -- 出库单价
--                 g.safety_stock                 -- 安全库存数
                goi.goods_name,                -- 11.27 全部取订单明细表
                goi.out_price,
                goi.safety_stock
            FROM ${this.orderDetailTable} goi
--             LEFT JOIN ${this.goodsTable} g ON goi.goods_id = g.goods_id
            LEFT JOIN ${this.supplierTable} s ON goi.supplier_id = s.supplier_id  -- 明细关联供应商
            LEFT JOIN ${this.categoryTable} ctg on goi.cate_id = ctg.cate_id        -- 食材分类表
            WHERE goi.order_id IN (${orderIds.map(() => '?').join(',')})
        `;
        const [allDetails] = await this.db.query(orderDetailsSql, orderIds);

        // 6. 组装数据：主表 + 明细（供应商信息在明细中）
        const orderMap = mainOrders.reduce((map, order) => {
            map[order.order_id] = { ...order, details: [] };
            return map;
        }, {});

        // 分配明细到对应订单
        allDetails.forEach(detail => {
            if (orderMap[detail.order_id]) {
                orderMap[detail.order_id].details.push(detail);
            }
        });

        const resultList = orderIds.map(orderId => orderMap[orderId]);

        return {
            list: resultList,
            pagination: {
                total,
                page: Number(page),
                pageSize: Number(pageSize),
                totalPages: Math.ceil(total / pageSize)
            }
        };
    }

    /**
     * 根据ID获取订单详情（包含明细、商品、供应商信息）
     * @param {number} orderId - 采购订单ID（goods_order.order_id）
     * @returns {Object|null} 订单完整信息或null（不存在时）
     */
    async getOrderById(orderId) {
        // 1. 校验订单ID
        if (isNaN(Number(orderId)) || Number(orderId) <= 0) {
            throw new Error('采购订单ID必须为有效的正整数');
        }
        const targetOrderId = Number(orderId);

        // 2. 并行查询主表、明细（含供应商信息）（提升效率）
        const [mainOrderResult, detailResult] = await Promise.all([
            // 查询订单主表（移除供应商相关字段，主表无supplier_id）
            this.db.query(`
                SELECT
                    go.order_id,
                    go.urgency_level,
                    go.title AS order_title,
                    go.code AS order_code,
                    go.order_date,
                    go.total_amount,
                    go.status,
                    go.created_uid,
                    u.user_name AS creator_name,
                    go.notes AS order_notes,
                    go.created_at,
                    go.updated_at
                FROM ${this.orderTable} go
                LEFT JOIN ${this.userTable} u ON go.created_uid = u.user_id
                WHERE go.order_id = ?
            `, [targetOrderId]),

            // 查询订单明细（从明细表关联供应商信息）
            this.db.query(`
                SELECT
                    goi.order_id,
                    goi.item_id,
--                     g.name AS goods_name,
                    goi.goods_id,
                    goi.unit AS goods_unit,
                    goi.spec AS goods_spec,
                    -- 从明细表获取供应商ID并关联供应商表
                    goi.supplier_id,
                    s.supplier_name,       -- 供应商名称
                    s.contact_person,      -- 供应商联系人
                    s.phone AS supplier_phone,  -- 供应商电话
                    goi.ordered_count,
                    goi.unit_price,
                    goi.amount,
                    goi.notes AS detail_notes,
                    -- 11.27 数据从订单明细表中取
                    goi.goods_name,
                    goi.out_price,
                    goi.cate_id,
                    goi.safety_stock
                FROM ${this.orderDetailTable} goi
                LEFT JOIN ${this.supplierTable} s ON goi.supplier_id = s.supplier_id  -- 关联供应商表
                WHERE goi.order_id = ?
            `, [targetOrderId])
        ]);

        // 3. 处理结果
        const mainOrder = mainOrderResult[0][0];
        if (!mainOrder) {
            return null; // 订单不存在
        }

        // 4. 补充：如果需要在主表级别显示"主要供应商"（可选逻辑）
        // 场景：若订单明细中供应商唯一，可提取到主表级展示
        let mainSupplier = null;
        if (detailResult[0].length > 0) {
            // 取第一条明细的供应商作为主供应商（仅当所有明细供应商相同时有效）
            const firstDetail = detailResult[0][0];
            mainSupplier = {
                supplier_id: firstDetail.supplier_id,
                supplier_name: firstDetail.supplier_name,
                contact_person: firstDetail.contact_person,
                supplier_phone: firstDetail.supplier_phone
            };
        }

        // 5. 组装最终结果
        return {
            ...mainOrder,
            main_supplier: mainSupplier, // 可选：主供应商信息（若有）
            details: detailResult[0]     // 明细数组（每条明细包含自身供应商信息）
        };
    }

    /**
     * 创建采购订单（包含明细，事务保证一致性）
     * @param {Object} orderData - 订单数据（主表+明细）
     * @param {number} orderData.createdUid - 创建人ID（必填）
     * @param {Array} orderData.details - 订单明细（必填，至少1条）
     * @param {number|null} [orderData.planId] - 关联采购计划ID（可选）

     * @param {string} [orderData.orderDate] - 订单归档日期（可选）
     * @param {string} [orderData.title] - 订单名称（可选）
     * @param {number} [orderData.urgencyLevel=1] - 紧急程度（默认1-普通，可选）
     * @param {string} [orderData.code] - 订单编号（可选）

     * @param {string} [orderData.notes] - 订单备注（可选）
     * @param {number} [orderData.status=1] - 订单状态（默认1-待发货，可选）
     * @returns {Object} 创建结果（订单ID、单号等）
     */
    async createOrder(orderData) {
        // 1. 参数校验
        const validateErrors = [];
        // 创建人ID校验
        if (isNaN(Number(orderData.createdUid)) || Number(orderData.createdUid) <= 0) {
            validateErrors.push('创建人ID必须为有效的正整数');
        }

        // 校验归档日期，没有就默认为今天的年月日
        const currentDate =dayjs().format('YYYY-MM-DD');
        orderData.orderDate = orderData.orderDate || currentDate;

        // 校验订单名称
        if (orderData.title === undefined || orderData.title.trim() === '') {
            validateErrors.push('订单名称不能为空');
        }
        // 紧急程度校验
        if (orderData.urgencyLevel === undefined || isNaN(Number(orderData.urgencyLevel)) || Number(orderData.urgencyLevel) < 1 || Number(orderData.urgencyLevel) > 3){
            validateErrors.push('紧急程度只能是1-普通、2-紧急、3-非常紧急');
        }
        // 明细校验
        if (!Array.isArray(orderData.details) || orderData.details.length === 0) {
            validateErrors.push('订单明细必须为非空数组，至少1条记录');
        } else {
            orderData.details.forEach((detail, index) => {
                if (isNaN(Number(detail.goodsId)) || Number(detail.goodsId) <= 0) {
                    validateErrors.push(`第${index + 1}条明细：商品ID必须为有效的正整数`);
                }
                if (isNaN(Number(detail.orderedCount)) || Number(detail.orderedCount) <= 0) {
                    validateErrors.push(`第${index + 1}条明细：订购数量必须为大于0的数字`);
                }
                if (isNaN(Number(detail.unitPrice)) || Number(detail.unitPrice) < 0) {
                    validateErrors.push(`第${index + 1}条明细：单价必须为非负数字`);
                }
            });
        }
        // 状态合法性校验（仅允许1-待发货、9-已作废）
        const allowedStatus = [1, 9];
        const targetStatus = orderData.status !== undefined ? Number(orderData.status) : 1;
        if (!allowedStatus.includes(targetStatus)) {
            validateErrors.push(`订单状态仅允许${allowedStatus.join('或')}（1-待发货，9-已作废）`);
        }
        // 校验供应商和创建人是否存在
        const [userResult] = await Promise.all([
            this.db.query(`SELECT user_id FROM ${this.userTable} WHERE user_id = ?`, [Number(orderData.createdUid)])
        ]);
        if (userResult[0].length === 0) {
            validateErrors.push(`创建人ID不存在：${orderData.createdUid}`);
        }
        // 抛出校验错误
        if (validateErrors.length > 0) {
            throw new Error(`参数校验失败：${validateErrors.join('；')}`);
        }

        // 2. 事务处理（创建主表+明细）
        let connection;
        try {
            // 获取连接并开启事务
            connection = await this.db.getConnection();
            await connection.beginTransaction();

            // 3. 生成订单业务单号（格式：PO+日期+3位序号，如PO20251025001）
            const dateStr = new Date().toISOString().slice(0, 10).replace(/-/g, '');
            const [maxCodeResult] = await connection.query(
                `SELECT code FROM ${this.orderTable} WHERE code LIKE ? ORDER BY code DESC LIMIT 1`,
                [`PO${dateStr}%`]
            );
            let seq = 1;
            if (maxCodeResult.length > 0) {
                seq = Number(maxCodeResult[0].code.slice(-3)) + 1;
            }
            const orderCode = `PO${dateStr}${seq.toString().padStart(3, '0')}`;

            // 4. 计算订单总金额（累加明细金额）
            let totalAmount = 0;
            orderData.details.forEach(detail => {
                const amount = Number(detail.orderedCount) * Number(detail.unitPrice);
                totalAmount += amount;
            });
            totalAmount = Number(totalAmount.toFixed(2)); // 适配decimal(12,2)

            // 5. 插入订单主表
            const [orderResult] = await connection.query(`
                INSERT INTO ${this.orderTable} (
                    code, order_date,title, urgency_level, total_amount, 
                    status, created_uid, updated_uid, notes, created_at, updated_at
                ) VALUES (?, ?, ?, ? , ?, ?, ?, ?, ?, NOW(), NOW())
            `, [
                orderCode,
                // orderData.planId ? Number(orderData.planId) : null, // 可选关联计划
                orderData.orderDate,
                orderData.title,
                orderData.urgencyLevel,
                totalAmount,
                targetStatus,
                Number(orderData.createdUid),
                Number(orderData.createdUid), // 更新人默认与创建人一致
                orderData.notes || ''
            ]);
            const newOrderId = orderResult.insertId;

            // 6. 批量插入订单明细
            const detailValues = orderData.details.map(detail => {
                const amount = Number((detail.orderedCount * detail.unitPrice).toFixed(2));
                return [
                    newOrderId,
                    Number(detail.goodsId),
                    Number(detail.orderedCount),
                    Number(detail.unitPrice),
                    detail.unit,
                    amount,
                    detail.notes || '',
                    detail.spec || '',
                    detail.supplierId ? Number(detail.supplierId) : null,
                    // 11.27 增加商品信息
                    detail.goods_name,
                    Number(detail.out_price),
                    detail.cate_id,
                    Number(detail.safety_stock)
                ];
            });
            const [detailResult] = await connection.query(`
                INSERT INTO ${this.orderDetailTable} (
                    order_id, goods_id, ordered_count, 
                    unit_price,unit, amount, notes,spec,supplier_id,
                    goods_name,out_price,cate_id,safety_stock -- 11.27新增
                ) VALUES ?
            `, [detailValues]);

            // 7. 提交事务
            await connection.commit();

            // 8. 返回创建结果
            return {
                orderId: newOrderId,
                orderCode: orderCode,
                totalAmount: totalAmount,
                status: targetStatus,
                mainAffectedRows: orderResult.affectedRows,
                detailAffectedRows: detailResult.affectedRows,
                detailCount: orderData.details.length
            };

        } catch (error) {
            // 回滚事务
            if (connection) {
                await connection.rollback();
            }
            console.error('创建采购订单失败：', error, { orderData });
            throw new Error(`创建采购订单失败：${error.message}`);

        } finally {
            // 释放连接
            if (connection) {
                connection.release();
            }
        }
    }

    /**
     * 更新采购订单（支持主表字段和明细全量更新，事务保证）
     * @param {number} orderId - 订单ID
     * @param {Object} updateData - 待更新数据
     * @param {number} [updateData.status] - 订单状态（可选）
     * @param {string} [updateData.notes] - 订单备注（可选）

     * @param {string} [updateData.title] - 订单名称（可选）}
     * @param {number} [updateData.urgencyLevel] - 订单紧急程度（可选）
     * @param {string} [updateData.orderDate] - 订单日期（可选）

     * @param {Array} [updateData.details] - 明细全量更新（可选）
     * @param {number} [updateData.updatedUid] - 更新人ID（必填，若有更新）
     * @returns {Object} 更新结果
     */
    async updateOrder(orderId, updateData) {
        // 1. 基础校验
        const validateErrors = [];
        if (isNaN(Number(orderId)) || Number(orderId) <= 0) {
            validateErrors.push('采购订单ID必须为有效的正整数');
        }
        // 若有更新操作，必须传更新人ID
        const hasUpdate = !!Object.keys(updateData).length;
        if (hasUpdate && (isNaN(Number(updateData.updatedUid)) || Number(updateData.updatedUid) <= 0)) {
            validateErrors.push('更新人ID必须为有效的正整数');
        }

        // 校验归档日期，没有就默认为今天的年月日
        const currentDate =dayjs().format('YYYY-MM-DD');
        updateData.orderDate = updateData.orderDate || currentDate;

        // 校验订单名称
        if (updateData.title === undefined || updateData.title.trim() === '') {
            validateErrors.push('订单名称不能为空');
        }
        // 紧急程度校验
        if (updateData.urgencyLevel === undefined || isNaN(Number(updateData.urgencyLevel)) || Number(updateData.urgencyLevel) < 1 || Number(updateData.urgencyLevel) > 3){
            validateErrors.push('紧急程度只能是1-普通、2-紧急、3-非常紧急');
        }

        // 状态合法性校验（允许所有合法状态）
        if (updateData.status !== undefined) {
            const allowedStatus = [1, 2, 3, 4, 5,6,0];
            const statusNum = Number(updateData.status);
            if (!allowedStatus.includes(statusNum)) {
                validateErrors.push(`订单状态仅允许${allowedStatus.join('、')}（1-待发货/2-已发货/3-部分入库/4-配送中/5-部分入库/6-已完成入库/0-已作废）`);
            }
        }
        // 明细校验（若传入）
        if (updateData.details !== undefined) {
            if (!Array.isArray(updateData.details)) {
                validateErrors.push('订单明细必须为数组类型');
            } else {
                updateData.details.forEach((detail, index) => {
                    if (isNaN(Number(detail.goodsId)) || Number(detail.goodsId) <= 0) {
                        validateErrors.push(`第${index + 1}条明细：商品ID必须为有效的正整数`);
                    }
                    if (isNaN(Number(detail.orderedCount)) || Number(detail.orderedCount) <= 0) {
                        validateErrors.push(`第${index + 1}条明细：订购数量必须为大于0的数字`);
                    }
                    if (isNaN(Number(detail.unitPrice)) || Number(detail.unitPrice) < 0) {
                        validateErrors.push(`第${index + 1}条明细：单价必须为非负数字`);
                    }
                });
            }
        }
        // 校验订单是否存在
        const existingOrder = await this.getOrderById(orderId);
        if (!existingOrder) {
            validateErrors.push(`采购订单ID=${orderId}不存在`);
        }
        // 抛出错误
        if (validateErrors.length > 0) {
            throw new Error(`参数校验失败：${validateErrors.join('；')}`);
        }

        // 2. 事务处理
        let connection;
        try {
            connection = await this.db.getConnection();
            await connection.beginTransaction();

            // 3. 更新订单主表（只更新传入的字段）
            const updateFields = [];
            const updateParams = [];

            if (updateData.status !== undefined) {
                updateFields.push('status = ?');
                updateParams.push(Number(updateData.status));
            }
            if (updateData.notes !== undefined) {
                updateFields.push('notes = ?');
                updateParams.push(updateData.notes);
            }
            // 必须更新更新时间和更新人
            updateFields.push('updated_at = NOW()');
            if (updateData.updatedUid) {
                updateFields.push('updated_uid = ?');
                updateParams.push(Number(updateData.updatedUid));
            }

            // 执行主表更新（若有字段需更新）
            if (updateFields.length > 0) {
                const updateSql = `
                    UPDATE ${this.orderTable} 
                    SET ${updateFields.join(', ')} 
                    WHERE order_id = ?
                `;
                await connection.query(updateSql, [...updateParams, Number(orderId)]);
            }

            // 4. 处理明细更新（全量更新：先删后插）
            let detailCount = existingOrder.details.length; // 默认保留原明细数量
            if (updateData.details !== undefined) {
                // 删除原有明细
                await connection.query(
                    `DELETE FROM ${this.orderDetailTable} WHERE order_id = ?`,
                    [Number(orderId)]
                );

                // 插入新明细（若数组非空）
                if (updateData.details.length > 0) {
                    const detailValues = updateData.details.map(detail => {
                        const amount = detail.amount || Number((detail.orderedCount * detail.unitPrice).toFixed(2));
                        return [
                            Number(orderId),
                            Number(detail.goodsId),
                            Number(detail.orderedCount),
                            Number(detail.unitPrice),
                            detail.unit,
                            amount,
                            detail.notes || '',
                            detail.supplierId ? Number(detail.supplierId) : null,
                            detail.spec || '',
                            // 11.27 增加商品信息
                            detail.goods_name,
                            Number(detail.out_price),
                            detail.cate_id,
                            Number(detail.safety_stock)
                        ];
                    });
                    await connection.query(`
                        INSERT INTO ${this.orderDetailTable} (
                            order_id, goods_id, ordered_count, 
                            unit_price, unit,amount, notes, supplier_id,spec,
                            goods_name,out_price,cate_id,safety_stock -- 11.27新增
                        ) VALUES ?
                    `, [detailValues]);
                    detailCount = updateData.details.length;
                } else {
                    detailCount = 0; // 若明细为空，更新为0
                }

                // 重新计算订单总金额（明细变更后需同步更新主表）
                let totalAmount = 0;
                updateData.details.forEach(detail => {
                    totalAmount += Number(detail.orderedCount) * Number(detail.unitPrice);
                });
                totalAmount = Number(totalAmount.toFixed(2));
                // "orderDate":"2025-11-06",  //归档日期
                //   "title":"11.06号物品采购", //采购订单名称
                //   "urgencyLevel":1,         //紧急程序
                await connection.query(`
                    UPDATE ${this.orderTable} 
                    SET total_amount = ?, 
                        updated_at = NOW() ,
                        order_date = ?,
                        title = ?,
                        urgency_level = ?
                    WHERE order_id = ?
                `, [totalAmount, updateData.orderDate, updateData.title, updateData.urgencyLevel,Number(orderId)]);
            }

            // 5. 提交事务
            await connection.commit();

            // 6. 返回更新结果
            return {
                orderId: Number(orderId),
                updatedFields: updateFields.length,
                currentDetailCount: detailCount,
                message: '采购订单更新成功'
            };

        } catch (error) {
            // 回滚事务
            if (connection) {
                await connection.rollback();
            }
            console.error('更新采购订单失败：', error, { orderId, updateData });
            throw new Error(`更新采购订单失败：${error.message}`);

        } finally {
            // 释放连接
            if (connection) {
                connection.release();
            }
        }
    }

    /**
     * 删除采购订单（级联删除明细，仅允许删除“待发货”状态的订单）
     * @param {number} orderId - 订单ID
     * @param {number} operatorId - 操作人ID（记录操作人）
     * @returns {Object} 删除结果
     */
    async deleteOrder(orderId, operatorId) {
        // 1. 校验参数
        if (isNaN(Number(orderId)) || Number(orderId) <= 0) {
            throw new Error('采购订单ID必须为有效的正整数');
        }
        if (isNaN(Number(operatorId)) || Number(operatorId) <= 0) {
            throw new Error('操作人ID必须为有效的正整数');
        }

        // 2. 校验订单状态（仅允许删除“待发货”状态）
        const existingOrder = await this.getOrderById(orderId);
        if (!existingOrder) {
            throw new Error(`采购订单ID=${orderId}不存在`);
        }
        if (existingOrder.status !== 1) {
            throw new Error(`仅允许删除“待发货”状态的订单，当前订单状态：${existingOrder.status}`);
        }

        // 3. 事务处理（先删明细，再删主表）
        let connection;
        try {
            connection = await this.db.getConnection();
            await connection.beginTransaction();

            // 4. 删除订单明细（外键约束：必须先删子表）
            const [detailDeleteResult] = await connection.query(
                `DELETE FROM ${this.orderDetailTable} WHERE order_id = ?`,
                [Number(orderId)]
            );

            // 5. 删除订单主表
            const [orderDeleteResult] = await connection.query(
                `DELETE FROM ${this.orderTable} WHERE order_id = ?`,
                [Number(orderId)]
            );

            // 6. 提交事务
            await connection.commit();

            // 7. 返回删除结果
            return {
                orderId: Number(orderId),
                deletedOrderRows: orderDeleteResult.affectedRows,
                deletedDetailRows: detailDeleteResult.affectedRows,
                operatorId: Number(operatorId)
            };

        } catch (error) {
            // 回滚事务
            if (connection) {
                await connection.rollback();
            }
            console.error('删除采购订单失败：', error, { orderId, operatorId });
            throw new Error(`删除采购订单失败：${error.message}`);

        } finally {
            // 释放连接
            if (connection) {
                connection.release();
            }
        }
    }

    /**
     * 更新订单状态（单独接口，控制状态流转合法性）
     * @param {number} orderId - 订单ID
     * @param {number} targetStatus - 目标状态
     * @param {number} operatorId - 操作人ID
     * @param {string} [notes] - 状态变更备注（可选）
     * @returns {Object} 状态更新结果
     * 订单状态：1-待确认, 2-取消, 3-已确认, 4-配送中, 5-部分入库, 6-已完成入库, 0-已作废（软删除）
    */
    async updateOrderStatus(orderId, targetStatus, operatorId, notes = '') {
        // 1. 参数校验
        const validateErrors = [];
        // 订单ID校验
        if (isNaN(Number(orderId)) || Number(orderId) <= 0) {
            validateErrors.push('采购订单ID必须为有效的正整数');
        }
        // 状态合法性校验（允许的状态值）
        const allowedStatus = [0, 1, 2, 3, 4, 5, 6]; // 最新状态集合
        const statusNum = Number(targetStatus);
        const statusTextMap = {
            1: '待确认',
            2: '取消',
            3: '已确认',
            4: '配送中',
            5: '部分入库',
            6: '已完成入库',
            0: '已作废（软删除）'
        };
        if (!allowedStatus.includes(statusNum)) {
            validateErrors.push(`目标状态无效，允许值：${allowedStatus.map(s => `${s}(${statusTextMap[s]})`).join('、')}`);
        }
        // 操作人校验
        if (isNaN(Number(operatorId)) || Number(operatorId) <= 0) {
            validateErrors.push('操作人ID必须为有效的正整数');
        }
        const [operatorResult] = await this.db.query(
            `SELECT user_id FROM ${this.userTable} WHERE user_id = ?`,
            [Number(operatorId)]
        );
        // 修复operatorResult的数组访问错误（原代码多了一层[0]）
        if (operatorResult.length === 0) {
            validateErrors.push(`操作人ID不存在：${operatorId}`);
        }
        // 抛出错误
        if (validateErrors.length > 0) {
            throw new Error(`参数校验失败：${validateErrors.join('；')}`);
        }

        // 2. 校验订单存在性和当前状态
        const existingOrder = await this.getOrderById(orderId);
        if (!existingOrder) {
            throw new Error(`采购订单ID=${orderId}不存在`);
        }
        const currentStatus = existingOrder.status;

        // 3. 状态流转合法性校验（严格控制业务流程）
        const validTransitions = {
            1: [2, 3, 0],        // 待确认 → 取消、已确认、已作废
            2: [0],               // 取消 → 仅允许作废（补充状态归档）
            3: [4, 0],            // 已确认 → 配送中、已作废
            4: [5, 6, 0],         // 配送中 → 部分入库、已完成入库、已作废
            5: [6, 0],            // 部分入库 → 已完成入库、已作废
            6: [0],               // 已完成入库 → 仅允许作废
            0: []                 // 已作废 → 不允许任何变更
        };
        if (!validTransitions[currentStatus].includes(statusNum)) {
            throw new Error(`状态流转不合法：不允许从「${statusTextMap[currentStatus]}」变更为「${statusTextMap[statusNum]}」`);
        }

        // 4. 执行状态更新
        try {
            const [updateResult] = await this.db.query(`
                UPDATE ${this.orderTable} 
                SET status = ?, updated_at = NOW(), updated_uid = ?, notes = CONCAT(IFNULL(notes, ''), '; ', ?)
                WHERE order_id = ?
            `,
                [
                    statusNum,
                    Number(operatorId),
                    `[${new Date().toLocaleString()}] 状态变更为${statusTextMap[statusNum]}（操作人：${operatorId}）${notes ? '，备注：' + notes : ''}`,
                    Number(orderId)
                ]);

            // 返回更新结果
            return {
                orderId: Number(orderId),
                oldStatus: currentStatus,
                oldStatusText: statusTextMap[currentStatus],
                newStatus: statusNum,
                newStatusText: statusTextMap[statusNum],
                operatorId: Number(operatorId),
                affectedRows: updateResult.affectedRows
            };

        } catch (error) {
            console.error('更新订单状态失败：', error, { orderId, targetStatus });
            throw new Error(`更新订单状态失败：${error.message}`);
        }
    }

    // 根据订单id检查订单是否存在
    async checkOrderExists(orderId) {
        try{
            const [result] = await this.db.query(`SELECT * FROM ${this.orderTable} WHERE order_id = ?`, [orderId]);
            return result.length > 0;
        }catch (err){
            throw new Error(`订单ID=${orderId}不存在`)
        }
    }
    // 根据订单id,订单商品项id 检查是否存在
    async checkOrderGoodsExists(orderId,itemID){
        try{
            const [result] = await this.db.query(`SELECT * FROM ${this.orderDetailTable} WHERE order_id = ? AND goods_id = ?`, [orderId, itemID]);
            return result.length > 0;
        }catch (err){
            throw new Error(`订单ID=${orderId}不存在`)
        }
    }
    // 删除订单商品项
    async deleteOrderGoods(orderId,goodsID){
        // 根据ItemID获取订单商品项的总价、总数
        const [item] = await this.db.query(`SELECT amount,ordered_count FROM ${this.orderDetailTable} WHERE order_id = ? AND goods_id = ?`, [orderId, goodsID]);

        if (!item || item.length === 0) {
            throw new Error('订单商品项不存在')
        }
        const { amount, ordered_count } = item[0];
        // 准备一个事务连接
        let connection;
        try {
            //开启事务
            connection = await this.db.getConnection();
            await connection.beginTransaction();

            //更新订单表中的总价
            const updateSql = `UPDATE ${this.orderTable} SET total_amount = total_amount - ? WHERE order_id = ?`;
            await this.db.query(updateSql, [amount, orderId]);
            // console.log('更新订单表成功')

            //删除明细项
            const deleteSql = `DELETE FROM ${this.orderDetailTable} WHERE order_id = ? AND goods_id = ?`;
            let [result] = await this.db.query(deleteSql, [orderId, goodsID]);
            // console.log('删除明细项成功')

            // 提交事务
            await connection.commit();

            if (result.affectedRows === 0) {
                throw new Error('删除不成功，请稍后再试');
            }
            return {
                orderId,
                goodsID,
                affectedRows: result.affectedRows
            };
        }catch (err){
            // 失败回滚
            await connection.rollback();
            throw new Error('删除失败',err);
        }finally {
            // 终止事务
            connection.release();
            console.log('事务结束')
        }

    }

    //删除采购单
    async deleteOrderRecord(orderId){
        let connection;
        console.log("准备操作数据库进行删除~")
        try{
            //开启事务
            connection = await this.db.getConnection();
            await connection.beginTransaction();

            //查询订单子表的商品数是否为0
            const [itemCount] = await this.db.query(`SELECT COUNT(*) AS item_count FROM ${this.orderDetailTable} WHERE order_id = ?`, [orderId]);
            console.log('查询子表商品数成功',itemCount,"orderId:",orderId)

            // 如果子表商品数不为0，则先删除子表
            if (itemCount[0].item_count > 0) {
                console.log('子表有商品，准备先进行删除')
                //进行删除
                const [deleteResult] = await this.db.query(`DELETE FROM ${this.orderDetailTable} WHERE order_id = ?`, [orderId]);
                console.log('删除子表成功',deleteResult)
            }

            console.log('子表商品数为0，准备删除主表')
            let [orderResult] = await this.db.query(`DELETE FROM ${this.orderTable} WHERE order_id = ?`, [orderId]);
            // 提交事务
            await connection.commit();
            console.log('删除主表成功',orderResult)

            return {
                orderId,
                affectedRows: orderResult.affectedRows
            };

        } catch (err){
            console.error('删除失败,信息如下：', err, { orderId });
            await connection.rollback();
            throw new Error('删除失败:'+err);
        } finally {
            connection.release();
        }
    }

    //
    async checkOrderGoodsExists2(orderId,goodsId){
        try{
            const [result] = await this.db.query(`SELECT * FROM ${this.orderDetailTable} WHERE order_id = ? AND goods_id = ?`, [orderId, goodsId]);
            return result.length > 0;
        }catch (err){
            throw new Error(`订单ID=${orderId}中不存在商品(ID):${goodsId}`)
        }
    }
    //为采购单添加商品项
    async addOrderGoodsItem(orderId,goodsId,goodsCount,notes){
        try{
            // 1.根据商品ID获取商品信息，从商品表、分类表，供应商表中获取商品完整信息
            let goodsInfoSql = `
                SELECT 
                    g.name as goods_name,
                    g.specification,
                    g.unit,
                    g.in_price,
                    g.supplier_id,
                    c.name as cate_name,
                    s.supplier_name,
                    g.out_price, -- 11.27新增
                    g.cate_id,
                    g.safety_stock
                FROM ${this.goodsTable}  AS g
                LEFT JOIN ${this.categoryTable} AS c ON g.cate_id = c.cate_id
                LEFT JOIN ${this.supplierTable} AS s ON g.supplier_id = s.supplier_id
                WHERE g.goods_id = ?
            `
            const [goodsInfo] = await this.db.query(goodsInfoSql, [goodsId]);
            const {
                goods_name,
                specification,
                unit,
                in_price,
                supplier_id,
                supplier_name,
                cate_name,
                // 11.27新增
                out_price,
                cate_id,
                safety_stock
            } = goodsInfo[0];

            // 2.添加商口项目到采购订单明细表
            const orderedCount = goodsCount;
            const unitPrice = in_price;
            const amount = orderedCount * unitPrice;
            // const notes = ''; //备注，直接传递过来，在编辑、查看的时候显示
            const fields = [
                'order_id',
                'goods_name',
                'goods_id',
                'spec',
                'unit',
                'unit_price',
                'ordered_count',
                'amount',
                'supplier_id',
                'notes',
                'out_price',
                'cate_id',
                'safety_stock'
            ];
            const addSql = `
                INSERT INTO ${this.orderDetailTable} (${fields.join(',')}) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)
            `;
            const values = [
                orderId,
                goods_name,
                goodsId,
                specification,
                unit,
                unitPrice,
                orderedCount,
                amount,
                supplier_id,
                notes,
                out_price,//11.27新增
                cate_id,
                safety_stock
            ];
            const [result] = await this.db.query(addSql, values);

            return {
                "item_id": result.insertId,
                "order_id":orderId,
                "goods_id":goodsId,
                "goods_name":goods_name,
                "spec":specification,
                "unit":unit,
                "unit_price":unitPrice,
                "ordered_count":orderedCount,
                "amount":amount,//采购总金额
                "notes":notes,
                "supplier_id":supplier_id,
                "supplier_name":supplier_name,
                "cate_name":cate_name,
                "out_price":out_price,//11.27新增
                "cate_id":cate_id,
                "safety_stock":safety_stock
            };
        } catch( err){
            throw new Error('添加商品项失败:'+err);
        }
    }
}

module.exports = ProcurementOrderModel;